package org.edu.modules.kwgl.controller;

import java.io.ByteArrayOutputStream;
import java.math.BigDecimal;
import java.math.RoundingMode;
import java.util.*;
import java.io.IOException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import cn.hutool.core.util.NumberUtil;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.aliyun.oss.ServiceException;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.google.common.collect.Lists;
import com.sun.javafx.tk.FontMetrics;
import org.apache.commons.lang.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.shiro.SecurityUtils;
import org.edu.common.system.base.controller.BaseController;
import org.edu.common.api.vo.Result;
import org.edu.common.system.query.QueryGenerator;
import org.edu.common.system.vo.LoginUser;
import org.edu.common.util.oConvertUtils;
import org.edu.modules.jxzy.entity.JwKecheng;
import org.edu.modules.jxzy.mapper.JwKechengMapper;
import org.edu.modules.kwgl.api.CommonApi;
import org.edu.modules.kwgl.api.IVeJwJxzyFeignClientService;
import org.edu.modules.kwgl.config.ConfigConstant;
import org.edu.modules.kwgl.entity.*;
import org.edu.modules.kwgl.service.IVeJwBkXueshengService;
import org.edu.modules.kwgl.service.IVeJwChengjiService;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import lombok.extern.slf4j.Slf4j;
import org.edu.modules.kwgl.service.IVeJwKaoshiService;
import org.edu.modules.kwgl.utils.CommonAipUtils;
import org.edu.modules.kwgl.utils.JwTextUtils;
import org.edu.modules.scheduleCommon.dto.PageDto;
import org.edu.modules.scheduleCommon.dto.PassRateStatistics;
import org.edu.modules.scheduleCommon.dto.ScoreAnalysis;
import org.edu.modules.scheduleCommon.entity.VeBaseAppUser;
import org.edu.modules.scheduleCommon.entity.VeBaseBanji;
import org.edu.modules.scheduleCommon.entity.VeBaseStudent;
import org.edu.modules.scheduleCommon.mapper.VeBaseBanjiMapper;
import org.edu.modules.scheduleCommon.service.VeBaseAppUserService;
import org.edu.modules.scheduleCommon.service.VeBaseStudentService;
import org.edu.modules.scheduleCommon.service.VeBaseTeacherService;
import org.jeecgframework.poi.excel.ExcelImportUtil;
import org.jeecgframework.poi.excel.entity.ExportParams;
import org.jeecgframework.poi.excel.entity.ImportParams;
import org.jeecgframework.poi.excel.entity.params.ExcelExportEntity;
import org.jeecgframework.poi.excel.export.ExcelExportServer;
import org.jeecgframework.poi.excel.view.JeecgEntityExcelView;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpHeaders;
import org.springframework.http.HttpStatus;
import org.springframework.http.MediaType;
import org.springframework.http.ResponseEntity;
import org.springframework.web.bind.annotation.*;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.servlet.ModelAndView;
import io.swagger.annotations.Api;
import io.swagger.annotations.ApiOperation;
import org.edu.common.aspect.annotation.AutoLog;

/**
 * @Description: 成绩
 * @Author: jeecg-boot
 * @Date: 2021-05-12
 * @Version: V1.0
 */
@Api(tags = "成绩")
@RestController
@RequestMapping("/kwgl/veJwChengji")
@Slf4j
public class VeJwChengjiController extends BaseController<VeJwChengji, IVeJwChengjiService> {
    @Autowired
    private IVeJwChengjiService veJwChengjiService;

    @Autowired
    private IVeJwBkXueshengService veJwBkXueshengService;

    @Autowired
    private IVeJwKaoshiService veJwKaoshiService;

    @Autowired
    private IVeJwJxzyFeignClientService veJwJxzyFeignClientService;

    @Autowired
    CommonApi commonApi;

    private MultipartHttpServletRequest multipartRequest;

    @Autowired
    private VeBaseAppUserService veBaseAppUserService;

    @Autowired
    private VeBaseStudentService veBaseStudentService;

    @Autowired
    private VeBaseTeacherService veBaseTeacherService;
    @Autowired
    private VeBaseBanjiMapper veBaseBanjiMapper;
    @Autowired
    private JwKechengMapper jwKechengMapper;

    /**
     * 分页列表查询
     *
     * @param veJwChengji
     * @param pageNo
     * @param pageSize
     * @param req
     * @return
     */
    @AutoLog(value = "成绩-分页列表查询")
    @ApiOperation(value = "成绩-分页列表查询", notes = "成绩-分页列表查询")
    @GetMapping(value = "/list")
    public Result<?> queryPageList(VeJwChengji veJwChengji,
                                   @RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
                                   @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
                                   HttpServletRequest req) {
        LoginUser loginUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
        if (oConvertUtils.isEmpty(loginUser)) {
            return Result.error("请登录系统！");
        }
        QueryWrapper<VeJwChengji> queryWrapper = QueryGenerator.initQueryWrapper(veJwChengji, req.getParameterMap());

        //根据用户id查用户信息
        VeBaseAppUser appUser = veBaseAppUserService.getVeBaseAppUserById(loginUser.getId());
        if (appUser.getUserType().contains("2")) {
            //根据学生学号查成绩
            queryWrapper.eq("XH", appUser.getUserId());
        } else if (appUser.getUserType().contains("1")) {
            //如果用户类型是1，为教师账号
            List<Integer> bjIdsList = veBaseTeacherService.getBjIdsListBySysUserId(0, appUser.getUserId());
            queryWrapper.in("xzbId", bjIdsList);
        }
        queryWrapper.orderByDesc("id");
//        Page<VeJwChengji> page = new Page<VeJwChengji>(pageNo, pageSize);
//        IPage<VeJwChengji> pageList = veJwChengjiService.page(page, queryWrapper);
        PageDto<VeJwChengji> page = new PageDto<>(pageNo, pageSize);
        IPage<VeJwChengji> pageList = veJwChengjiService.pageDto(page, queryWrapper);

        //查询是否加入补考名单
        for (VeJwChengji item : pageList.getRecords()) {
            if (veJwBkXueshengService.getByStuidAndKcid(item.getStuid(), item.getKcid()) == null) {
                item.setIsJoinBk(0);
            } else {
                item.setIsJoinBk(1);
            }
            VeJwKaoshi veJwKaoshi = veJwKaoshiService.getById(item.getKsid());
            if (veJwKaoshi != null) {
                item.setKsrq(veJwKaoshi.getKsrq());
            }
            item.setAudittidText(JwTextUtils.getText(item.getAudittid()));
        }
        return Result.ok(pageList);
    }


    /**
     * 学生录入信息查询
     *
     * @param veJwChengji
     * @param pageNo
     * @param pageSize
     * @param req
     * @return
     */
    @AutoLog(value = "成绩-学生录入信息查询")
    @ApiOperation(value = "成绩-学生录入信息查询", notes = "成绩-学生录入信息查询")
    @GetMapping(value = "/lrlist")
    public Result<?> lrlist(VeJwChengji veJwChengji,
                            @RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
                            @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
                            HttpServletRequest req) {
        VeJwKaoshi jwKaoshi = veJwKaoshiService.getById(veJwChengji.getKsid());
        LambdaQueryWrapper<VeJwChengji> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(VeJwChengji::getKsid, veJwChengji.getKsid());
        List<VeJwChengji> dataList = veJwChengjiService.list(queryWrapper);
        Map<String, VeJwChengji> chengjiMap = new HashMap<>();
        for (VeJwChengji item : dataList) {
            chengjiMap.put(item.getXh(), item);
        }
//        String kcmc=veJwJxzyFeignClientService.getKcNameById(jwKaoshi.getKcid());
        List<VeJwChengji> exportList = new ArrayList<>();
        if (jwKaoshi != null && jwKaoshi.getBjid() != null) {
            for (String xzbId : jwKaoshi.getBjid().split(",")) {
                if (StringUtils.isEmpty(xzbId)) {
                    continue;
                }
                JSONArray objectList = CommonAipUtils.getXueshengByBjid(Integer.valueOf(xzbId));
                if (objectList != null && objectList.size() > 0) {
                    for (int i = 0; i < objectList.size(); i++) {
                        JSONObject student = objectList.getJSONObject(i);
                        VeJwChengji zhiye = new VeJwChengji();
                        zhiye.setXh(student.getString("xh"));
                        zhiye.setXm(student.getString("xm"));
                        zhiye.setFalid(student.getInteger("fal_id"));
                        zhiye.setXzbid(student.getInteger("bj_id"));
                        zhiye.setGradeid(student.getInteger("grade_id"));
                        zhiye.setSpecid(student.getInteger("spec_id"));
                        zhiye.setXzid(student.getInteger("xz"));
                        zhiye.setTerminalid(student.getInteger("terminal_id"));
                        zhiye.setStuid(student.getInteger("id"));
                        zhiye.setKcid(jwKaoshi.getKcid());
                        zhiye.setKsid(jwKaoshi.getId());
                        if (chengjiMap.containsKey(zhiye.getXh())) {
                            exportList.add(chengjiMap.get(zhiye.getXh()));
                        } else {
                            exportList.add(zhiye);
                        }
                    }
                }
            }
        }
        return Result.ok(exportList);
    }

    /**
     * 添加
     *
     * @param veJwChengji
     * @return
     */
    @AutoLog(value = "成绩-添加")
    @ApiOperation(value = "成绩-添加", notes = "成绩-添加")
    @PostMapping(value = "/add")
    public Result<?> add(@RequestBody VeJwChengji veJwChengji) {
        veJwChengjiService.save(veJwChengji);
        return Result.ok("添加成功！");
    }

    /**
     * 批量通过
     *
     * @param jsonObject
     * @return
     */
    @AutoLog(value = "成绩类型-通过")
    @ApiOperation(value = "成绩类型-通过", notes = "成绩类型-通过")
    @PutMapping(value = "/stateBatch")
    public Result<?> stateBatch(@RequestBody JSONObject jsonObject) {
        if (!jsonObject.containsKey("ids")) {
            return Result.error("参数错误!");
        }
        String ids = jsonObject.getString("ids");
        VeJwChengji data = new VeJwChengji();
        data.setAuditstatus(ConfigConstant.AUDIT_AGREE);
        LambdaQueryWrapper<VeJwChengji> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.in(VeJwChengji::getId, Arrays.asList(ids.split(",")));
        veJwChengjiService.update(data, queryWrapper);
        return Result.ok("通过成功");
    }


    /**
     * 编辑
     *
     * @param veJwChengji
     * @return
     */
    @AutoLog(value = "成绩-编辑")
    @ApiOperation(value = "成绩-编辑", notes = "成绩-编辑")
    @PutMapping(value = "/edit")
    public Result<?> edit(@RequestBody VeJwChengji veJwChengji) {
        VeJwChengji old = veJwChengjiService.getById(veJwChengji.getId());
        if (old == null) {
            return Result.error("记录不存在!");
        }
        if (old.getAuditstatus() == ConfigConstant.AUDIT_AGREE) {
            return Result.error("记录已审核!");
        }
        //若无关联考试成绩置为0
        if (veJwChengji.getHasexam() == 0) {
            veJwChengji.setKccj(new BigDecimal(0.0));
        }
        //如果是考试形式为【考试】
        if (veJwChengji.getKsxsid() == 1) {
            //分数超过100置为0
            if (veJwChengji.getKccj().compareTo(new BigDecimal("100.0")) > 1) {
                veJwChengji.setKccj(new BigDecimal("0.0"));
            }
        }
        veJwChengjiService.updateById(veJwChengji);
        return Result.ok("编辑成功!");
    }

    /**
     * 审核
     *
     * @param veJwChengji
     * @return
     */
    @AutoLog(value = "成绩-审核")
    @ApiOperation(value = "成绩-审核", notes = "成绩-审核")
    @PutMapping(value = "/audit")
    public Result<?> audit(@RequestBody VeJwChengji veJwChengji) {
        VeJwChengji old = veJwChengjiService.getById(veJwChengji.getId());
        if (old == null) {
            return Result.error("记录不存在!");
        }
//		 if(old.getAuditstatus() == ConfigConstant.AUDIT_AGREE){
//			 return Result.error("记录已审核!");
//		 }
        VeJwChengji data = new VeJwChengji();
        data.setId(old.getId());
        if (veJwChengji.getAudittidText() != null) {
            data.setAudittid(JwTextUtils.addText(veJwChengji.getAudittidText()));
        }
        data.setAuditstatus(veJwChengji.getAuditstatus());
        veJwChengjiService.updateById(data);
        return Result.ok("处理成功!");
    }

    /**
     * 通过id删除
     *
     * @param id
     * @return
     */
    @AutoLog(value = "成绩-通过id删除")
    @ApiOperation(value = "成绩-通过id删除", notes = "成绩-通过id删除")
    @DeleteMapping(value = "/delete")
    public Result<?> delete(@RequestParam(name = "id", required = true) String id) {
        veJwChengjiService.removeById(id);
        return Result.ok("删除成功!");
    }

    /**
     * 批量删除
     *
     * @param ids
     * @return
     */
    @AutoLog(value = "成绩-批量删除")
    @ApiOperation(value = "成绩-批量删除", notes = "成绩-批量删除")
    @DeleteMapping(value = "/deleteBatch")
    public Result<?> deleteBatch(@RequestParam(name = "ids", required = true) String ids) {
        this.veJwChengjiService.removeByIds(Arrays.asList(ids.split(",")));
        return Result.ok("批量删除成功!");
    }

    /**
     * 通过id查询
     *
     * @param id
     * @return
     */
    @AutoLog(value = "成绩-通过id查询")
    @ApiOperation(value = "成绩-通过id查询", notes = "成绩-通过id查询")
    @GetMapping(value = "/queryById")
    public Result<?> queryById(@RequestParam(name = "id", required = true) String id) {
        VeJwChengji veJwChengji = veJwChengjiService.getById(id);
        if (veJwChengji == null) {
            return Result.error("未找到对应数据");
        }
        return Result.ok(veJwChengji);
    }


    /**
     * 根据班级id获取班级成绩情况
     *
     * @param bjid
     * @return
     */
    @AutoLog(value = "成绩-通过id查询")
    @ApiOperation(value = "成绩-通过id查询", notes = "成绩-通过id查询")
    @GetMapping(value = "/queryClassCj")
    public Result<?> queryClassCj(@RequestParam(name = "bjid", required = true) Integer bjid,
                                  @RequestParam(name = "gradeid", required = false) String gradeid,
                                  @RequestParam(name = "semid", required = true) String semid
    ) {
        LambdaQueryWrapper<VeJwChengji> queryWrapper = new LambdaQueryWrapper<>();
//        queryWrapper.eq(VeJwChengji::getGradeid,gradeid);
        queryWrapper.eq(VeJwChengji::getSemid, semid);
        queryWrapper.eq(VeJwChengji::getXzbid, bjid);
        List<VeJwChengji> chengjiList = veJwChengjiService.list(queryWrapper);

        List<JSONObject> kechengList = new ArrayList<>();
        Map<Integer, String> kcMap = new HashMap<>();
        Map<Integer, JSONObject> kechengInfo = new HashMap<>();
        Map map = new HashMap();
        if (chengjiList.size() > 0) {
            for (VeJwChengji item : chengjiList) {
                if (!kcMap.containsKey(item.getKcid())) {
                    kcMap.put(item.getKcid(), veJwJxzyFeignClientService.getKcNameById(item.getKcid()));
                    JSONObject kechengJson = new JSONObject();
                    kechengJson.put("id", item.getKcid());
                    kechengJson.put("name", kcMap.get(item.getKcid()));
                    kechengList.add(kechengJson);
                }
                if (!kechengInfo.containsKey(item.getKcid())) {
                    kechengInfo.put(item.getKcid(), new JSONObject());
                }
                kechengInfo.get(item.getKcid()).put(item.getXh(), item.getKccj());
            }
        }
        map.put("kechengList", kechengList);
        map.put("kechengInfo", kechengInfo);
        return Result.ok(map);
    }

    /**
     * 导出excel
     *
     * @param request
     * @param veJwChengji
     */
    @RequestMapping(value = "/exportXls")
    public ModelAndView exportXls(HttpServletRequest request, VeJwChengji veJwChengji) {
        return super.exportXls(request, veJwChengji, VeJwChengji.class, "成绩");
    }


    /**
     * 通过excel导入数据
     *
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "/importExcel", method = RequestMethod.POST)
    public Result<?> importExcel(HttpServletRequest request, HttpServletResponse response) {
        return super.importExcel(request, response, VeJwChengji.class);
    }


    /**
     * 批量更新成绩
     *
     * @param list
     * @return
     */
    @AutoLog(value = "考试成绩管理-批量更新成绩")
    @ApiOperation(value = "考试成绩管理-批量更新成绩", notes = "考试成绩管理-批量更新成绩")
    @PutMapping(value = "/updateCjBatch")
    public Result<?> updateCjBatch(@RequestBody List<VeJwChengji> list) {

        //获取课程总分
        BigDecimal ksfs = new BigDecimal(veJwJxzyFeignClientService.getKcKsfsById(list.get(0).getKcid()));

        //及格分数
        BigDecimal jgfs = ksfs.multiply(new BigDecimal("0.6"));
//		 List<VeJwChengji> bkcjs = list.stream().filter(item -> item.getKccj()
//				 .compareTo(ksfs)<=0).collect(Collectors.toList());

//		 if(bkcjs.size()<list.size()){
//			 return Result.error("学生成绩不得大于课程总分！请检查！");
//		 }

        for (VeJwChengji item : list) {
            if (item.getKccj() != null) {
                if (item.getKccj().compareTo(ksfs) > 0) {
                    return Result.error("学生成绩不得大于课程总分！请检查！");
                }
                if (item.getKccj().compareTo(jgfs) >= 0) {
                    item.setSfjg(1);
                } else {
                    item.setSfjg(0);
                }
            }

        }
        veJwChengjiService.saveOrUpdateBatch(list);

        return Result.ok("考试成绩提交成功!");
    }


    /**
     * 导出excel
     *
     * @param request
     * @param veJwChengji
     */
//    @RequestMapping(value = "/exportXlsKaoshi")
    public ModelAndView exportXlsKaoshi(HttpServletRequest request, VeJwChengji veJwChengji) {
        VeJwKaoshi jwKaoshi = veJwKaoshiService.getById(veJwChengji.getKsid());

        LambdaQueryWrapper<VeJwChengji> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(VeJwChengji::getKsid, veJwChengji.getKsid());
        List<VeJwChengji> dataList = veJwChengjiService.list(queryWrapper);
        Map<String, VeJwChengji> chengjiMap = new HashMap<>();
        for (VeJwChengji item : dataList) {
            chengjiMap.put(item.getXh(), item);
        }


        String kcmc = veJwJxzyFeignClientService.getKcNameById(jwKaoshi.getKcid());

        List<VeJwChengji> exportList = new ArrayList<>();
        if (jwKaoshi != null && jwKaoshi.getBjid() != null) {
            for (String xzbId : jwKaoshi.getBjid().split(",")) {
                if (StringUtils.isEmpty(xzbId)) {
                    continue;
                }
                JSONArray objectList = CommonAipUtils.getXueshengByBjid(Integer.valueOf(xzbId));
                if (objectList != null && objectList.size() > 0) {
                    for (int i = 0; i < objectList.size(); i++) {
                        JSONObject student = objectList.getJSONObject(i);
                        VeJwChengji zhiye = new VeJwChengji();
                        zhiye.setStuid(student.getInteger("id"));
                        zhiye.setXh(student.getString("xh"));
                        zhiye.setXm(student.getString("xm"));
                        zhiye.setFalid(student.getInteger("fal_id"));
                        zhiye.setXzbid(student.getInteger("bj_id"));
                        zhiye.setGradeid(student.getInteger("grade_id"));
                        zhiye.setSpecid(student.getInteger("spec_id"));
                        zhiye.setXzid(student.getInteger("xz"));
                        zhiye.setTerminalid(student.getInteger("terminal_id"));
                        exportList.add(zhiye);
                    }
                }
            }
        }

        List<VeJwChengjiExcel> array = new ArrayList<>();
//		map存key减少重复查询
        Map<Integer, String> zybMap = CommonAipUtils.getZybIdNameMap();
        Map<Integer, String> njMap = CommonAipUtils.getGradeIdNameMap();
        Map<Integer, String> xqMap = CommonAipUtils.getXueqiIdName();
        Map<Integer, String> zyMap = new HashMap<>();
        Map<Integer, String> banjiMap = new HashMap<>();

        Map<Integer, String> zyrzmcMap = new HashMap<>();
        for (VeJwChengji item : exportList) {
            VeJwChengjiExcel excelItem = new VeJwChengjiExcel();


            excelItem.setXh(item.getXh());
            excelItem.setXm(item.getXm());

//			专业部名称
            if (item.getFalid() != null && zybMap.containsKey(item.getFalid())) {
                excelItem.setFalidName(zybMap.get(item.getFalid()));
            }
//			年级名称
            if (item.getGradeid() != null && njMap.containsKey(item.getGradeid())) {
                excelItem.setGradeidName(njMap.get(item.getGradeid()));
            }
//			获取专业名称
            if (item.getSpecid() != null) {
                if (!zyMap.containsKey(item.getSpecid())) {
                    zyMap.put(item.getSpecid(), CommonAipUtils.getZyById(item.getSpecid()));
                }
                excelItem.setSpecidName(zyMap.get(item.getSpecid()));
            }

            if (chengjiMap.containsKey(item.getXh())) {
                excelItem.setCjidName(chengjiMap.get(item.getXh()).getKccj().toString());
                excelItem.setCj(chengjiMap.get(item.getXh()).getSfjg() == 1 ? "通过" : "不通过");
            }
//			获取专业名称
            if (item.getSemid() != null && xqMap.containsKey(item.getSemid())) {
                excelItem.setSemidName(xqMap.get(item.getSemid()));
            }

            excelItem.setKcidName(kcmc);

            array.add(excelItem);
        }


        LoginUser sysUser = (LoginUser) SecurityUtils.getSubject().getPrincipal();
        ModelAndView mv = new ModelAndView(new JeecgEntityExcelView());
        mv.addObject("fileName", "成绩");
        mv.addObject("entity", VeJwChengjiExcel.class);
        mv.addObject("params", new ExportParams("成绩报表", "导出人:" + sysUser.getRealname(), "成绩"));
        mv.addObject("data", array);
        return mv;
    }

    /**
     * 通过excel导入数据
     *
     * @param request
     * @param response
     * @return
     */
    @RequestMapping(value = "/importExcelKaoshi", method = RequestMethod.POST)
    public Result<?> importExcelKaoshi(HttpServletRequest request, HttpServletResponse response) {
        MultipartHttpServletRequest multipartRequest = (MultipartHttpServletRequest) request;
        String ksid = request.getParameter("ksid");
        Map<String, MultipartFile> fileMap = multipartRequest.getFileMap();
        Iterator var6 = fileMap.entrySet().iterator();
        if (var6.hasNext()) {
            Map.Entry<String, MultipartFile> entity = (Map.Entry) var6.next();
            MultipartFile file = (MultipartFile) entity.getValue();
            ImportParams params = new ImportParams();
            params.setTitleRows(1);
            params.setHeadRows(1);
            params.setNeedSave(true);

            Result var11;
            try {
                VeJwKaoshi jwKaoshi = veJwKaoshiService.getById(Integer.parseInt(ksid));
                //map存key减少重复查询
//                Map<String, Integer> zybMap = CommonAipUtils.getZybNameIdMap();
//                Map<Integer, String> njMap = CommonAipUtils.getGradeIdNameMap();
//                Map<Integer, String> zyMap = new HashMap<>();
//                Map<Integer, String> banjiMap = new HashMap<>();

                List<VeJwChengjiExcel> list = ExcelImportUtil.importExcel(file.getInputStream(), VeJwChengjiExcel.class, params);
                long start = System.currentTimeMillis();
                List<VeJwChengji> targetList = new ArrayList<>();
                int success = 0;
                int error = 0;
                for (VeJwChengjiExcel item : list) {
                    VeJwChengji zhiye = getCjByXhAndKs(item.getXh(), item.getXm(), Integer.parseInt(ksid));
//					不存在旧数据就新建
                    if (zhiye == null) {
                        zhiye = new VeJwChengji();
                        JSONObject student = CommonAipUtils.getXueshengByXh(item.getXh(), item.getXm());
                        if (student == null) {
                            error++;
                            continue;
                        }
                        zhiye.setXh(item.getXh());
                        zhiye.setXm(item.getXm());
                        zhiye.setStuid(student.getInteger("id"));
                        zhiye.setAuditstatus(0);
                        zhiye.setFalid(student.getInteger("fal_id"));
                        zhiye.setXzbid(student.getInteger("bj_id"));
                        zhiye.setGradeid(student.getInteger("grade_id"));
                        zhiye.setSpecid(student.getInteger("spec_id"));
                        zhiye.setXzid(student.getInteger("xz"));
                        zhiye.setTerminalid(student.getInteger("terminal_id"));
                        zhiye.setCjlrrqsj(new Date());
                        zhiye.setKsid(jwKaoshi.getId());
                        zhiye.setKcid(jwKaoshi.getKcid());
                        zhiye.setSemid(jwKaoshi.getSemid());
                    }
                    if (StringUtils.isNotEmpty(item.getCjidName())) {
                        zhiye.setKccj(new BigDecimal(item.getCjidName()));
                    }
                    if (StringUtils.isNotEmpty(item.getCj())) {
                        zhiye.setSfjg(item.getCj().trim().equals("通过") ? 1 : 0);
                    }
                    targetList.add(zhiye);
                    success++;
                }
                this.veJwChengjiService.saveOrUpdateBatch(targetList);
                log.info("消耗时间" + (System.currentTimeMillis() - start) + "毫秒");
                if (error == 0) {
                    return Result.ok("文件导入成功！数据行数：" + targetList.size());
                } else {
                    return Result.ok("文件导入成功！成功" + success + "条，失败" + error + "条");
                }
            } catch (Exception var23) {
                log.error(var23.getMessage(), var23);
                var11 = Result.error("文件导入失败:" + var23.getMessage());
            } finally {
                try {
                    file.getInputStream().close();
                } catch (IOException var22) {
                    var22.printStackTrace();
                }

            }

            return var11;
        } else {
            return Result.error("文件导入失败！");
        }
    }

    /**
     * 导出excel
     *
     * @param request
     * @param veJwChengji
     */
    @RequestMapping(value = "/exportXlsKaoshi")
    public ResponseEntity<byte[]> dynamicExport(HttpServletRequest request, VeJwChengji veJwChengji, HttpServletResponse response) throws IOException {
        //考试集合
        List<VeJwKaoshi> veJwKaoshiList = new ArrayList<>();
        //导出数据集合
        List<Map<String, Object>> dataList = new ArrayList<>();
        //课程总分集合
        Map<String, BigDecimal> kcTotalScoreMap = new HashMap<>();
        //学生总数
        int totalStudent = 0;
//        班级信息行
        String bjInfo = "";
        try {
            // ================= 3. 准备数据 =================
            VeBaseBanji veBaseBanji = veBaseBanjiMapper.selectById(veJwChengji.getXzbid());
            if (veBaseBanji == null) {
                throw new RuntimeException("查无班级信息");
            }

            dataList = new ArrayList<>();
            LambdaQueryWrapper<VeBaseStudent> veBaseStudentLambdaQueryWrapper = new LambdaQueryWrapper<>();
            veBaseStudentLambdaQueryWrapper.eq(VeBaseStudent::getBjId, veJwChengji.getXzbid());
            veBaseStudentLambdaQueryWrapper.orderByAsc(VeBaseStudent::getXh);
            List<VeBaseStudent> veBaseStudentList = veBaseStudentService.list(veBaseStudentLambdaQueryWrapper);
            if (veBaseStudentList == null || veBaseStudentList.size() == 0) {
                throw new RuntimeException("查无学生数据");
            }
            totalStudent = veBaseStudentList.size();
            //            年级：2023   系/部：专业教学部   专业：美术绘画   行政班级：202301   排序规则：按学号升序   学生人数：49

            String space = "   ";
            bjInfo = "年级：" + veBaseStudentList.get(0).getGradeText() + space
                    + "系/部：" + veBaseStudentList.get(0).getFalText() + space
                    + "专业：" + veBaseStudentList.get(0).getSpecText() + space
                    + "行政班级：" + veBaseBanji.getXzbdm() + space
                    + "排序规则：按学号升序" + space
                    + "学生人数：" + totalStudent;
            //查询所有课程信息
            LambdaQueryWrapper<JwKecheng> jwKechengLambdaQueryWrapper = new LambdaQueryWrapper<>();
            jwKechengLambdaQueryWrapper.eq(JwKecheng::getStatus, 1);
            jwKechengLambdaQueryWrapper.eq(JwKecheng::getIsdelete, 0);
            List<JwKecheng> jwKechengList = jwKechengMapper.selectList(new QueryWrapper<>());
            if (jwKechengList == null || jwKechengList.size() == 0) {
                throw new RuntimeException("查无课程信息");
            }
            //课程id对应的课程性质
            Map<Integer, Integer> kcAndKcxzMap = new HashMap<>();
            for (JwKecheng jwKecheng : jwKechengList) {
                kcAndKcxzMap.put(jwKecheng.getId(), jwKecheng.getKcxz());
            }

            LambdaQueryWrapper<VeJwKaoshi> veJwKaoshiLambdaQueryWrapper = new LambdaQueryWrapper<>();
            veJwKaoshiLambdaQueryWrapper.eq(VeJwKaoshi::getBjid, veJwChengji.getXzbid());
//            veJwKaoshiLambdaQueryWrapper.eq(VeJwKaoshi::getGradeid, student.getGradeId());
            veJwKaoshiLambdaQueryWrapper.orderByAsc(VeJwKaoshi::getKsrq, VeJwKaoshi::getName);
            veJwKaoshiList = veJwKaoshiService.list(veJwKaoshiLambdaQueryWrapper);
            if (veJwKaoshiList == null || veJwKaoshiList.size() == 0) {
                throw new RuntimeException("查无考试信息");
            }


            for (VeBaseStudent student : veBaseStudentList) {
                Map<String, Object> dataMap = new HashMap<>();

                // 基础信息
                dataMap.put("学号", student.getXh());
                dataMap.put("姓名", student.getXm());

                // 动态课程成绩
                BigDecimal total = BigDecimal.ZERO;
                BigDecimal cultureTotal = BigDecimal.ZERO;
                BigDecimal majorTotal = BigDecimal.ZERO;
                BigDecimal cultureAvg = BigDecimal.ZERO;
                BigDecimal majorAvg = BigDecimal.ZERO;
                BigDecimal avgScore = BigDecimal.ZERO;
                Integer cultureCount = 0;//文化考试数量
                Integer majorCount = 0;//专业考试数量

                for (VeJwKaoshi kaoshi : veJwKaoshiList) {
                    String kaoshiName = kaoshi.getName();
                    LambdaQueryWrapper<VeJwChengji> veJwChengjiLambdaQueryWrapper = new LambdaQueryWrapper<>();
                    veJwChengjiLambdaQueryWrapper.eq(VeJwChengji::getKsid, kaoshi.getId());
                    veJwChengjiLambdaQueryWrapper.eq(VeJwChengji::getStuid, student.getId());
                    VeJwChengji veJwChengji1 = veJwChengjiService.getOne(veJwChengjiLambdaQueryWrapper);
                    BigDecimal chengji = BigDecimal.ZERO;
                    if (veJwChengji1 != null) {
                        chengji = veJwChengji1.getKccj();
                    } else {

                    }
                    dataMap.put(kaoshiName, chengji);
                    //计算课程总分
                    BigDecimal kcTotalScore = kcTotalScoreMap.get(kaoshiName);
                    if (kcTotalScore != null) {
                        kcTotalScoreMap.put(kaoshiName, kcTotalScore.add(chengji));
                    } else {
                        kcTotalScoreMap.put(kaoshiName, chengji);
                    }
                    //计算总分
                    total = total.add(chengji);
                    //计算文化平均分
                    if (kcAndKcxzMap.get(kaoshi.getKcid()) == 1) {
                        cultureTotal = cultureTotal.add(chengji);
                        cultureCount++;
                        cultureAvg = cultureTotal.divide(BigDecimal.valueOf(cultureCount), 2, RoundingMode.HALF_DOWN);
                    } else if (kcAndKcxzMap.get(kaoshi.getKcid()) == 3) {//计算专业平均分
                        majorTotal = majorTotal.add(chengji);
                        majorCount++;
                        majorAvg = majorTotal.divide(BigDecimal.valueOf(majorCount), 2, RoundingMode.HALF_DOWN);
                    }
                }
                //计算平均分
                avgScore = total.divide(BigDecimal.valueOf(veJwKaoshiList.size()), 2, RoundingMode.HALF_DOWN);
                // 统计信息
                dataMap.put("总成绩", total);
                dataMap.put("文化课平均成绩", cultureAvg);
                dataMap.put("专业课平均成绩", majorAvg);
                dataMap.put("平均成绩", avgScore);
                dataList.add(dataMap);
            }
        } catch (RuntimeException e) {
            System.out.println(e.getMessage());
        }

        try (Workbook workbook = new XSSFWorkbook();) {
            // 创建Sheet1并填充数据
            Sheet sheet1 = workbook.createSheet("Sheet1");

            // 总列数
            Integer colCount = 3 + veJwKaoshiList.size() + 4 - 1;//序号、学号、姓名、考试成绩。。。总成绩、文化课平均成绩、专业课平均成绩、平均成绩
            // 假设第0列需要自适应（列索引从0开始）
            for (int i = 0; i < colCount; i++) {
                sheet1.autoSizeColumn(i);
            }
            // 创建第一行（标题行）
            Row titleRow = sheet1.createRow(0);
            sheet1.addMergedRegion(new CellRangeAddress(0, 0, 0, colCount));
            Cell titleCell = titleRow.createCell(0);
            titleCell.setCellValue("中央美术学院附属中等美术学校学生成绩"); //  这个位置添加  表头标题 自己设置
            // 设置标题单元格样式，使其居中
            CellStyle titleStyle = workbook.createCellStyle();
            titleStyle.setAlignment(HorizontalAlignment.CENTER);
            titleStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            titleCell.setCellStyle(titleStyle);

            Row title2Row = sheet1.createRow(1);
            sheet1.addMergedRegion(new CellRangeAddress(1, 1, 0, colCount));
            Cell title2Cell = title2Row.createCell(0);
            title2Cell.setCellValue("入学以来"); //  这个位置添加  表头标题 自己设置
            // 设置标题单元格样式，使其居中
            CellStyle title2Style = workbook.createCellStyle();
            title2Style.setAlignment(HorizontalAlignment.CENTER);
            title2Style.setVerticalAlignment(VerticalAlignment.CENTER);
            title2Cell.setCellStyle(title2Style);


            // 创建第三行（班级信息行）
            Row bjInfoRow1 = sheet1.createRow(2);
            bjInfoRow1.createCell(0).setCellValue(bjInfo);
            // 合并半数列
//            sheet1.addMergedRegion(new CellRangeAddress(2, 2, 0, (int) Math.ceil((double)  colCount / 2)));
            sheet1.addMergedRegion(new CellRangeAddress(2, 2, 0, colCount));


            //表头行
            Row headerRow1 = sheet1.createRow(3);
            headerRow1.createCell(0).setCellValue("序号");
            headerRow1.createCell(1).setCellValue("学号");
            headerRow1.createCell(2).setCellValue("姓名");
            int j = 3;
            for (VeJwKaoshi veJwKaoshi : veJwKaoshiList) {
                headerRow1.createCell(j).setCellValue(veJwKaoshi.getName());
                j++;
            }
            headerRow1.createCell(j++).setCellValue("总成绩");
            headerRow1.createCell(j++).setCellValue("文化课平均成绩");
            headerRow1.createCell(j++).setCellValue("专业课平均成绩");
            headerRow1.createCell(j++).setCellValue("平均成绩");

            //数据
            for (int rowIndex = 0; rowIndex < dataList.size(); rowIndex++) {
                Row dataRow = sheet1.createRow(rowIndex + 4);
                Map<String, Object> data = dataList.get(rowIndex);
                // 填充序号
                dataRow.createCell(0).setCellValue(rowIndex + 1);
                //学号
                dataRow.createCell(1).setCellValue(String.valueOf(data.get("学号")));
                //姓名
                dataRow.createCell(2).setCellValue(String.valueOf(data.get("姓名")));
                //各科成绩
                int k = 3;
                for (VeJwKaoshi veJwKaoshi : veJwKaoshiList) {
                    dataRow.createCell(k).setCellValue(String.valueOf(data.get(veJwKaoshi.getName())));
                    k++;
                }
                dataRow.createCell(k++).setCellValue(String.valueOf(data.get("总成绩")));
                dataRow.createCell(k++).setCellValue(String.valueOf(data.get("文化课平均成绩")));
                dataRow.createCell(k++).setCellValue(String.valueOf(data.get("专业课平均成绩")));
                dataRow.createCell(k++).setCellValue(String.valueOf(data.get("平均成绩")));

            }
            //数据统计行
            Row statisticsRow = sheet1.createRow(dataList.size() + 4);
            statisticsRow.createCell(0).setCellValue("课程/环节平均成绩");
            // 合并前三列
            sheet1.addMergedRegion(new CellRangeAddress(dataList.size() + 4, dataList.size() + 4, 0, 2));

            int h = 3;
            for (VeJwKaoshi veJwKaoshi : veJwKaoshiList) {
                BigDecimal totalScore = kcTotalScoreMap.get(veJwKaoshi.getName());
                statisticsRow.createCell(h).setCellValue(String.valueOf(totalScore.divide(BigDecimal.valueOf(totalStudent), 2, RoundingMode.HALF_DOWN)));
                h++;
            }

            // 设置单元格样式：边框和字体居中
            CellStyle style = workbook.createCellStyle();
            style.setAlignment(HorizontalAlignment.CENTER);
            style.setVerticalAlignment(VerticalAlignment.CENTER);
            style.setBorderTop(BorderStyle.THIN);
            style.setBorderBottom(BorderStyle.THIN);
            style.setBorderLeft(BorderStyle.THIN);
            style.setBorderRight(BorderStyle.THIN);


            // 定义参数
            float fontSize = 11; // 字体大小（磅）
            float charWidthPerPoint = 0.2f; // 经验系数（不同字体需调整）
            // 应用样式到表头
            for (int i = 0; i < colCount + 1; i++) {
                Cell cell1 = headerRow1.getCell(i);
                if (cell1 != null) {
                    cell1.setCellStyle(style);
                    //设置单元格宽度sta
                    //单个字符估算宽度
                    float oneLength = fontSize * charWidthPerPoint * 256;
                    String content = cell1.getStringCellValue();
                    // 估算公式：（字符数+冗余字符数） × 单个字符估算宽度
                    int estimatedWidth = (int) ((content.length() + 2) * oneLength);
                    sheet1.setColumnWidth(i, Math.min(estimatedWidth, 255 * 256)); // 限制最大宽度
                    //设置单元格宽度end
                }
//                Cell cell2 = headerRow2.getCell(i);
//                if (cell2 != null) {
//                    cell2.setCellStyle(style);
//                }
            }

            // 应用样式到数据行，两个标题行+一个班级信息行+表头行+数据行+统计行
            for (int rowIndex = 4; rowIndex < dataList.size() + 4 + 1; rowIndex++) {
                Row row = sheet1.getRow(rowIndex);
                if (row != null) {
                    for (int colIndex = 0; colIndex < colCount + 1; colIndex++) {
                        Cell cell = row.getCell(colIndex);
                        if (cell == null) {
                            row.createCell(colIndex);
                            cell = row.getCell(colIndex);
                        }
                        cell.setCellStyle(style);
                    }
                }
            }


            // 写入文件
            //        try (FileOutputStream outputStream = new FileOutputStream("导出表格.xlsx")) {
            //            workbook.write(outputStream);
            //        }

            // 将工作簿写入字节数组输出流
            ByteArrayOutputStream bos = new ByteArrayOutputStream();
            workbook.write(bos);
            byte[] excelBytes = bos.toByteArray();

            // 设置HTTP响应头
            HttpHeaders headers = new HttpHeaders();
            headers.setContentType(MediaType.APPLICATION_OCTET_STREAM);
            headers.setContentDispositionFormData("attachment", "导出表格.xlsx");

            return new ResponseEntity<>(excelBytes, headers, HttpStatus.OK);
//        workbook.close();
//            System.out.println("Excel file has been created successfully.");
        } catch (IOException e) {
            e.printStackTrace();
            return new ResponseEntity<>(HttpStatus.INTERNAL_SERVER_ERROR);
        }

    }


    /**
     * 学生成绩统计-成绩查询
     *
     * @param veJwChengji 查询条件
     * @param pageNo      页号
     * @param pageSize    页显示数
     * @param req         请求流
     * @return 成绩列表
     */
    @AutoLog(value = "学生成绩统计-成绩查询")
    @ApiOperation(value = "学生成绩统计-成绩查询", notes = "学生成绩统计-成绩查询")
    @GetMapping("/pageList")
    public Result<?> queryList(VeJwChengji veJwChengji,
                               @RequestParam(name = "pageNo", defaultValue = "1") Integer pageNo,
                               @RequestParam(name = "pageSize", defaultValue = "10") Integer pageSize,
                               HttpServletRequest req) {
        QueryWrapper<VeJwChengji> queryWrapper = QueryGenerator.initQueryWrapper(veJwChengji, req.getParameterMap());
        // 审核状态：通过
        queryWrapper.eq("auditstatus", 1);
        // 根据学号进行排序
        queryWrapper.getExpression().getOrderBy().clear();
        queryWrapper.orderByAsc("xh");

        Page<VeJwChengji> page = new Page<>(pageNo, pageSize);
        IPage<VeJwChengji> pageList = veJwChengjiService.page(page, queryWrapper);

        return Result.OK(pageList);
    }

    /**
     * 学生成绩统计-及格率统计
     *
     * @param veJwChengji 查询条件
     * @return
     */
    @AutoLog(value = "学生成绩统计-及格率统计")
    @ApiOperation(value = "学生成绩统计-及格率统计", notes = "学生成绩统计-及格率统计")
    @GetMapping("/statistics")
    public Result<?> statistics(VeJwChengji veJwChengji) {
        // 必须传入学期
        if (veJwChengji == null || veJwChengji.getSemid() == null) {
            return Result.error("请选择学期");
        }
        // 获取所有的专业组
        List<JSONObject> zyzList = commonApi.queryFacultyList(CommonApi.COMMON_TOKEN_KEY).getResult();
        List<JSONObject> zyzs = Lists.newArrayList();
        for (JSONObject zyz : zyzList) {
            if (Objects.equals(zyz.getInteger("id"), veJwChengji.getFalid()) || veJwChengji.getFalid() == null) {
                zyz.put("jgl", veJwChengjiService.caleJgl(veJwChengji, zyz.getInteger("id")));
                zyzs.add(zyz);
            }
        }

        return Result.OK(zyzs);
    }

    /**
     * 学生成绩统计-及格率趋势
     *
     * @return
     */
    @AutoLog(value = "学生成绩统计-及格率趋势")
    @ApiOperation(value = "学生成绩统计-及格率趋势", notes = "学生成绩统计-及格率趋势")
    @GetMapping("/trend")
    public Result<?> trend(VeJwChengji veJwChengji) {

        Map<String, JSONObject> data = veJwChengjiService.queryAllJgl();
        // 返回数据
        JSONObject result = new JSONObject();
        // 添加所有学期
        result.put("xqs", commonApi.querySemesterList(CommonApi.COMMON_TOKEN_KEY).getResult());
        // 获取所有的专业组
        List<JSONObject> zyzs = commonApi.queryFacultyList(CommonApi.COMMON_TOKEN_KEY).getResult();
        for (JSONObject zyz : zyzs) {
            List<JSONObject> xqs = commonApi.querySemesterList(CommonApi.COMMON_TOKEN_KEY).getResult();
            for (JSONObject xq : xqs) {
                JSONObject jglData = data.get(zyz.getInteger("id") + "_" + xq.getInteger("id"));
                // 如果不存在该专业组该学期的及格率数据，则直接赋值为0
                if (jglData == null) {
                    xq.put("jgl", 0);
                    continue;
                }
                xq.put("jgl", jglData.getBigDecimal("jgl"));
            }
            zyz.put("xqs", xqs);
        }
        result.put("zyzs", zyzs);
        return Result.OK(result);
    }

    private VeJwChengji getCjByXhAndKs(String xh, String xm, Integer ksid) {
        if (org.springframework.util.StringUtils.isEmpty(xm)) {
            return null;
        }
        LambdaQueryWrapper<VeJwChengji> queryWrapper = new LambdaQueryWrapper<>();
        queryWrapper.eq(VeJwChengji::getXm, xm);
        queryWrapper.eq(VeJwChengji::getXh, xh);
        queryWrapper.eq(VeJwChengji::getKsid, ksid);
        queryWrapper.last(" limit 1 ");
        return veJwChengjiService.getOne(queryWrapper);
    }

    /**
     * 成绩分析
     *
     * @param falId
     * @param specId
     * @return
     */
    @AutoLog(value = "成绩分析")
    @ApiOperation(value = "成绩分析", notes = "成绩分析")
    @GetMapping("/scoreAnalysis")
    public Result<?> scoreAnalysis(@RequestParam(name = "falId", required = false) Integer falId,
                                   @RequestParam(name = "specId", required = false) Integer specId,
                                   @RequestParam(name = "gradeId", required = false) Integer gradeId,
                                   @RequestParam(name = "semId", required = false) Integer semId,
                                   @RequestParam(name = "kcId", required = false) Integer kcId
    ) {
        List<ScoreAnalysis> scoreAnalysisList = veJwChengjiService.scoreAnalysis(falId, specId, gradeId, semId, kcId);
        return Result.OK(scoreAnalysisList);
    }

    /**
     * 及格率统计
     *
     * @param falId
     * @param specId
     * @return
     */
    @AutoLog(value = "及格率统计")
    @ApiOperation(value = "及格率统计", notes = "及格率统计")
    @GetMapping("/passRateStatistics")
    public Result<?> passRateStatistics(@RequestParam(name = "falId", required = false) Integer falId,
                                   @RequestParam(name = "specId", required = false) Integer specId,
                                   @RequestParam(name = "gradeId", required = false) Integer gradeId,
                                   @RequestParam(name = "semId", required = false) Integer semId,
                                   @RequestParam(name = "xzbId", required = false) Integer xzbId
    ) {
        List<PassRateStatistics> passRateStatisticsList = veJwChengjiService.passRateStatistics(falId, specId, gradeId, semId, xzbId);
        return Result.OK(passRateStatisticsList);
    }



}
